﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_SaveAnnouncement')
BEGIN
    PRINT 'Dropping Procedure proc_cm_SaveAnnouncement'
    DROP  Procedure  proc_cm_SaveAnnouncement
END
GO

PRINT 'Creating Procedure proc_cm_SaveAnnouncement'
GO

CREATE PROCEDURE [dbo].[proc_cm_SaveAnnouncement]
    @pHtmlPageId uniqueidentifier
    , @pAnnouncementId uniqueidentifier
	, @pAnnouncementTitleTxt nvarchar(100) = null
    , @pAnnouncementTxt nvarchar(max)
    , @pEffectiveFrom datetime = null
    , @pEffectiveTo datetime = null
	, @pUserName nvarchar(255) = null
	, @pSectionCd nvarchar(50) = 'Main'
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @vUserId UNIQUEIDENTIFIER

	SELECT @vUserId = UserId FROM aspnet_Users WHERE [UserName] = @pUserName

	UPDATE [dbo].[tbl_announcement]
	SET 
		[announcement_txt] = @pAnnouncementTxt
		,[announcement_title_txt] = @pAnnouncementTitleTxt
		,[effective_start_dttm] = @pEffectiveFrom
		,[effective_end_dttm] = @pEffectiveTo
		,[update_dttm] = GETDATE()
		,[UserId] = @vUserId
	WHERE
		[announcement_id] = @pAnnouncementId 
		AND [html_page_id] = @pHtmlPageId
		AND [section_cd] = @pSectionCd

	if @@ROWCOUNT = 0
	begin
		INSERT INTO [dbo].[tbl_announcement]
		(
			[announcement_id]
			,[html_page_id]
			,[announcement_title_txt]
			,[announcement_txt]
			,[effective_start_dttm]
			,[effective_end_dttm]
			,[create_dttm]
			,[update_dttm]
			,[UserId] 
			,[section_cd]
		)
		SELECT
			@pAnnouncementId
			, @pHtmlPageId
			, @pAnnouncementTitleTxt
			, @pAnnouncementTxt
			, @pEffectiveFrom
			, @pEffectiveTo
			, GETDATE()
			, GETDATE()
			, @vUserId
			, @pSectionCd
	end
END
GO

GRANT EXEC ON dbo.proc_cm_SaveAnnouncement TO PUBLIC
GO
